package breeze.framework.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * @author breeze
 * @date 2021年1月10日
 * @email breeze_java@163.com
 * @description
 */
@SuppressWarnings("deprecation")
public class Excel {

	private static CellStyle newStyle;
	private static DataFormatter df = new DataFormatter();
	private static Pattern p = Pattern.compile("\\s*|\t|\r|\n");
	
	public static List<String[][]> readExcel(String path){
		try {
			return readExcel(new FileInputStream(path));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return null;
		}
	}
	public static List<String[][]> readExcel(File file){
		try {
			return readExcel(new FileInputStream(file));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static List<String[][]> readExcel(InputStream in){
		try {
			List<String[][]> list = new ArrayList<String[][]>();
			Workbook wb = WorkbookFactory.create(in);//XSSF HSSF
			FormulaEvaluator formulaEval = wb.getCreationHelper().createFormulaEvaluator();
			int size = wb.getNumberOfSheets();
			for (int i = 0; i < size; i++) {
				//System.out.println("页签"+i+"的名称为："+wb.getNameAt(i));
				String[][] result = null;
				// 工作表
				Sheet sheet = (Sheet) wb.getSheetAt(i);
				// 行数,一般会多一些行
				int hb = sheet.getPhysicalNumberOfRows();
				// 列数,取出最大列
				int lb = 0;
				for (int j = 0; j < hb; j++) {
					if (null != sheet.getRow(j)) {
						int temp = sheet.getRow(j).getPhysicalNumberOfCells();
						if (temp > lb)
							lb = temp;
					}
				}
				result = new String[hb][lb];
				for (int h = 0; h < sheet.getPhysicalNumberOfRows(); h++) {
					// 获得工作表的行
					Row row = sheet.getRow(h);
					if (null != row) {
						for (int l = 0; l < lb; l++) {
							// 获得工作表的列
							Cell cell = row.getCell((short) l);
							if (null != cell) {
								try {
									if (cell.getCellType().getClass() == CellType.FORMULA.getClass()) {
				                        result[h][l] = df.formatCellValue(cell, formulaEval);
				                    } else {
				                    	result[h][l] = df.formatCellValue(cell);
				                    }
								} catch (Exception e) {
									try {
										BigDecimal decimal = new BigDecimal(cell.getNumericCellValue());
										result[h][l] = decimal.toPlainString();
									} catch (Exception e2) {
										try {
											result[h][l] = cell.getStringCellValue();
										} catch (Exception e3) {
											e3.printStackTrace();
											result[h][l] = df.formatCellValue(cell);
										}
									}
								}
							}else {
								result[h][l] = null;
							}
							if(null != result[h][l] && !"".equals(result[h][l])) {
								Matcher m = p.matcher(result[h][l]);
								result[h][l] = m.replaceAll("");
							}
						}
					}
				}
				list.add(result);
			}
			return list;
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				in.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return new ArrayList<String[][]>();
	}
	
	
	public static void copyRow(Workbook wb, Row fromRow, Row toRow, boolean copyValueFlag) {
	    toRow.setHeight(fromRow.getHeight());

	    for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
	        Cell tmpCell = cellIt.next();
	        Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
	        copyCell(wb, tmpCell, newCell, copyValueFlag);
	    }

	    Sheet worksheet = fromRow.getSheet();

	    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
	        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
	        if (cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {
	            CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(),
	                    (toRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
	                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
	            worksheet.addMergedRegion(newCellRangeAddress);
	        }
	    }
	}
	
	/**
	 * 复制单元格
	 * @param srcCell
	 * @param distCell
	 * @param copyValueFlag true则连同cell的内容一起复制
	 * Caused by: java.lang.IllegalStateException: The maximum number of cell styles was exceeded. 
	 * You can define up to 4000 styles in a .xls workbook
	 * 创建了过多的CellStyle.
	 */
	public static void copyCell(Workbook wb, Cell srcCell, Cell distCell, boolean copyValueFlag) {
		if(null == newStyle){
			newStyle = wb.createCellStyle();
		    CellStyle srcStyle = srcCell.getCellStyle();
			newStyle.cloneStyleFrom(srcStyle);
			newStyle.setFont(wb.getFontAt(srcStyle.getFontIndex()));
		}

	    /**
	     * 设置样式
	     * This Style does not belong to the supplied Workbook.
		 *Are you trying to assign a style from one workbook to the cell of a differnt workbook?
	     */
		try {
		    distCell.setCellStyle(newStyle);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	    //内容
	    if (srcCell.getCellComment() != null) {
	        distCell.setCellComment(srcCell.getCellComment());
	    }

	    //不同数据类型处理
	    CellType srcCellType = srcCell.getCellType();
 		distCell.setCellType(srcCellType);
 		if (copyValueFlag) {
 			if (srcCellType.getClass() == CellType.NUMERIC.getClass()) {
 				if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
 					distCell.setCellValue(srcCell.getDateCellValue());
 				} else {
 					distCell.setCellValue(srcCell.getNumericCellValue());
 				}
 			} else if (srcCellType.getClass() == CellType.STRING.getClass()) {
 				distCell.setCellValue(srcCell.getRichStringCellValue());
 			} else if (srcCellType.getClass() == CellType.BLANK.getClass()) {
 				// nothing21
 			} else if (srcCellType.getClass() == CellType.BOOLEAN.getClass()) {
 				distCell.setCellValue(srcCell.getBooleanCellValue());
 			} else if (srcCellType.getClass() == CellType.ERROR.getClass()) {
 				distCell.setCellErrorValue(srcCell.getErrorCellValue());
 			} else if (srcCellType.getClass() == CellType.FORMULA.getClass()) {
 				distCell.setCellFormula(srcCell.getCellFormula());
 			} else {}
 		}

	}
	
	/**
	 * 基于原来的模板 ,未考虑增删行和公式
	 */
	public static boolean writeBackToExcel(String path,List<String[][]> list){
		try {
			Workbook wb = WorkbookFactory.create(new FileInputStream(path));//XSSF HSSF
			//int size = wb.getNumberOfSheets();
			for (int i = 0; i < list.size(); i++) {
				//System.out.println("页签"+i+"的名称为："+wb.getNameAt(i));
				//String[][] result = null;
				// 工作表
				Sheet sheet = (Sheet) wb.getSheetAt(i);
				// 行数,一般会多一些行
				int hb = sheet.getPhysicalNumberOfRows();
				// 列数,取出最大列
				int lb = 0;
				for (int j = 0; j < hb; j++) {
					if (null != sheet.getRow(j)) {
						int temp = sheet.getRow(j).getPhysicalNumberOfCells();
						if (temp > lb)
							lb = temp;
					}
				}
				String[][] data = list.get(i);
				for (int h = 0; h < sheet.getPhysicalNumberOfRows(); h++) {
					// 获得工作表的行
					Row row = sheet.getRow(h);
					if (null != row) {
						for (int l = 0; l < row.getPhysicalNumberOfCells(); l++) {
							// 获得工作表的列
							Cell cell = row.getCell((short) l);
							if (null != cell) {
								try {
									cell.setCellValue(data[h][l]);
								} catch (Exception e) {
									break;
								}
							}
						}
					}
				}
			}
			FileOutputStream fos = new FileOutputStream(path);
			wb.write(fos);
			fos.flush();
			fos.close();
			return true;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	/*
	 * 读写一体，未完成
	 */
	public static boolean writeBackToExcel(Workbook wb,String path,List<String[][]> list){
		try {
			for (int i = 0; i < list.size(); i++) {
				Sheet sheet = (Sheet) wb.getSheetAt(i);
				String[][] data = list.get(i);
				for (int h = 0; h < data.length; h++) {
					Row row = sheet.getRow(h);
					if (null != row && null !=data[h] ) {
						for (int k = 0; k < data[i].length; k++) {
							Cell cell = row.getCell((short) k);
							if (null != cell && null != data[h][k]) {
								cell.setCellValue(data[h][k]);
							}
						}
					}
				}
			}
			FileOutputStream fos = new FileOutputStream(path);
			wb.write(fos);
			fos.flush();
			fos.close();
			return true;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} 
		return false;
	}
	
    public static Sheet createSheet(String fileName, String sheetName)   
    {   
        Workbook wb = new HSSFWorkbook();   
        FileOutputStream fileOut = null;   
        Sheet sheet = null;   
           
        try {   
           fileOut = new FileOutputStream(fileName);   
           sheet = wb.createSheet(sheetName);   
           wb.write(fileOut);   
           fileOut.close();   
        } catch (FileNotFoundException e1) {   
            e1.printStackTrace();   
        } catch (IOException e) {   
            e.printStackTrace();
        }
        try {
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
        return sheet;   
    }   
       
    public static void saveSheet(String fileName, Sheet sheet)   
    {   
        FileOutputStream fileOut = null;   
           
        try {   
            fileOut = new FileOutputStream(fileName);   
            sheet.getWorkbook().write(fileOut);   
            fileOut.close();   
        } catch (FileNotFoundException e) {   
            e.printStackTrace();
        } catch (IOException e) {   
            e.printStackTrace();
        }   
    }   


	public static void write(String path, List<String[][]> sheets_data,List<String> sheets_name) {

		HSSFWorkbook wb = null;
		try {
			wb = new HSSFWorkbook();
			for (int i = 0; i < sheets_data.size(); i++) {
				String name = sheets_name.get(i);
				String[][] sheet_data = sheets_data.get(i);
				
				HSSFSheet sheet = wb.createSheet(name);
				for (int j = 0; j < sheet_data.length; j++) {
					//创建行
					HSSFRow row = sheet.createRow((short) j);

					for (int k = 0; k < sheet_data[j].length; k++) {
						// 创建格 createCell((short) lie)
						HSSFCell cell = row.createCell((short) k);
						cell.setCellValue(sheet_data[j][k]);
					}
				}

				FileOutputStream fileout = new FileOutputStream(path);
				wb.write(fileout);
				fileout.close();

			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		try {
			if(null!=wb)wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 将sheet页的名称作为sheet的第一行大标题(按列总数合并单元格)
	 * 设置内容水平居中,垂直居中
	 */
	public static void writeExcelWithTitle(String path,String[][] sheet_data,String sheet_name) {

		int column = sheet_data[0].length;
		HSSFWorkbook wb = null;
		try {
			wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet(sheet_name);
			HSSFRow title_row = sheet.createRow(0);
			title_row.setHeight((short)200);
			for (int i = 0; i < column; i++) {
				HSSFCell title_cell = title_row.createCell(i);
				HSSFCellStyle cellStyle = title_cell.getCellStyle();
				cellStyle.setAlignment(HorizontalAlignment.CENTER);
				cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
			}
			//合并单元格
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, column));
			HSSFCell title_cell = title_row.getCell(0);
			title_cell.setCellValue(sheet_name);
			
			for (int j = 0; j < sheet_data.length; j++) {
				//创建行
				HSSFRow row = sheet.createRow((short)j+1);

				for (int k = 0; k < sheet_data[j].length; k++) {
					// 创建格 createCell((short) lie)
					HSSFCell cell = row.createCell((short) k);
					cell.setCellValue(sheet_data[j][k]);
				}
			}
			
			FileOutputStream fileout = new FileOutputStream(path);
			wb.write(fileout);
			fileout.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(null != wb) wb.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	

	@Deprecated
	public boolean creatNewExcel(HSSFWorkbook targetWorkbook,
			HSSFSheet sourceSheet, String targetPath, String newSheetName,
			String targetWorkbookName) {

		// 新的excel 文件名
		String excelName = targetWorkbookName;
		String strPath = targetPath;// 保存新EXCEL路径
		// 创建新的excel
		// HSSFWorkbook wbCreat = new HSSFWorkbook();
		HSSFSheet sheetCreat = targetWorkbook.createSheet(newSheetName);

		// 复制源表中的合并单元格
		MergerRegion(sheetCreat, sourceSheet);

		int firstRow = sourceSheet.getFirstRowNum();
		int lastRow = sourceSheet.getLastRowNum();
		for (int i = firstRow; i <= lastRow; i++) {

			// 创建新建excel Sheet的行
			HSSFRow rowCreat = sheetCreat.createRow(i);

			// 取得源有excel Sheet的行
			HSSFRow row = sourceSheet.getRow(i);
			if (null == row) {
				continue;
			}

			// 单元格式样
			HSSFCellStyle cellStyle = null;

			int firstCell = row.getFirstCellNum();
			int lastCell = row.getLastCellNum();
			for (short j = (short) firstCell; j < lastCell; j++) {

				// 自动适应列宽 貌似不起作用
				sheetCreat.autoSizeColumn(j);

				// new一个式样
				cellStyle = targetWorkbook.createCellStyle();

				// 设置边框线型
				if (null == row.getCell(j)) {
					continue;
				}
				cellStyle.setBorderTop(row.getCell(j).getCellStyle()
						.getBorderTop());
				cellStyle.setBorderBottom(row.getCell(j).getCellStyle()
						.getBorderBottom());
				cellStyle.setBorderLeft(row.getCell(j).getCellStyle()
						.getBorderLeft());
				cellStyle.setBorderRight(row.getCell(j).getCellStyle()
						.getBorderRight());

				// 设置内容位置:例水平居中,居右，居工
				cellStyle.setAlignment(row.getCell(j).getCellStyle()
						.getAlignment());
				// 设置内容位置:例垂直居中,居上，居下
				cellStyle.setVerticalAlignment(row.getCell(j).getCellStyle()
						.getVerticalAlignment());

				// 自动换行
				cellStyle.setWrapText(row.getCell(j).getCellStyle()
						.getWrapText());
				rowCreat.createCell(j).setCellStyle(cellStyle);
				// 设置单元格高度
				rowCreat.getCell(j).getRow()
						.setHeight(row.getCell(j).getRow().getHeight());
				// 设置单元格颜色
				// cellStyle.setFillBackgroundColor(row.getCell(j).getCellStyle().getFillBackgroundColor());
				// cellStyle.setFont(row.getCell(j).getCellStyle().getFont(sourceSheet.getWorkbook()));
				// 单元格类型
				switch (row.getCell(j).getCellType()) {
//					case HSSFCell.CELL_TYPE_STRING:
//						String strVal = removeInternalBlank(row.getCell(j)
//								.getStringCellValue());
//	
//						rowCreat.getCell(j).setCellValue(strVal);
//						break;
//	
//					case HSSFCell.CELL_TYPE_NUMERIC:
//						rowCreat.getCell(j).setCellValue(
//								row.getCell(j).getNumericCellValue());
//						break;
//	
//					case HSSFCell.CELL_TYPE_FORMULA:
//						try {
//							rowCreat.getCell(j).setCellValue(
//									String.valueOf(row.getCell(j)
//											.getNumericCellValue()));
//						} catch (IllegalStateException e) {
//							try {
//								rowCreat.getCell(j).setCellValue(
//										String.valueOf(row.getCell(j)
//												.getRichStringCellValue()));
//							} catch (Exception ex) {
//								rowCreat.getCell(j).setCellValue("公式出错");
//							}
//						}
//						break;
					default:
						break;
				}
			}
		}

		// 检查同名
		// excelName = checkFileName(strPath,excelName);

		FileOutputStream fileOut;
		try {
			fileOut = new FileOutputStream(strPath + excelName + ".xls");
			targetWorkbook.write(fileOut);
			fileOut.close();
			return true;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return false;
	}

	public static String checkFileName(String strPath, String checkFilename) {
		File file = new File(strPath);
		for (File f : file.listFiles()) {
			if (f.getName().equals(checkFilename)) {
				checkFilename += checkFilename + "_重名";
				checkFileName(strPath, checkFilename);
				break;
			}
		}
		return checkFilename;
	}

	/**
     * 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
     * 行和列都是从0开始计数，且起始结束都会合并
     * 这里是合并excel中日期的两行为一行
     */
	@Deprecated
	public static void MergerRegion(HSSFSheet sheetCreat, HSSFSheet sheet) {
    	//sheet.addMergedRegion(new CellRangeAddress(start_row, end_row, start_column, end_column));
//		int sheetMergerCount = sheet.getNumMergedRegions();
//		for (int i = 0; i < sheetMergerCount; i++) {
//			Region mergedRegionAt = sheet.getMergedRegionAt(i);
//			sheetCreat.addMergedRegion(mergedRegionAt);
//		}		
	}

	public static boolean isInMergerCellRegion(HSSFSheet sheet,int intCellRow, int intCellCol) throws IOException {
		boolean retVal = false;

		int sheetMergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergerCount; i++) {
			CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i);
			// 合并单元格CELL起始行
			int firstRow = cra.getFirstRow();
			// 合并单元格CELL起始列
			int firstCol = cra.getFirstColumn();

			// 合并单元格CELL结束行
			int lastRow = cra.getFirstColumn();
			// 合并单元格CELL结束列
			int lastCol = cra.getLastColumn();

			if (intCellRow >= firstRow && intCellRow <= lastRow) {
				if (intCellCol >= firstCol && intCellCol <= lastCol) {
					retVal = true;
					break;
				}
			}
		}
		return retVal;
	}

	public static String removeInternalBlank(String s) {
		// System.out.println("bb:" + s);
		Pattern p = Pattern.compile("\\s*|\t|\r|\n");
		Matcher m = p.matcher(s);
		char str[] = s.toCharArray();
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < str.length; i++) {
			if (str[i] == ' ') {
				sb.append(' ');
			} else {
				break;
			}
		}
		String after = m.replaceAll("");
		return sb.toString() + after;
	}
	
	public static void main(String[] args) {
//		writeExcelWithTitle("e:\\test\\aa.xlsx", null, "werwer");
		
		
		final int startRow = 4;
		final int startColumn = 1;
		final File data_file = new File("C:\\Users\\breeze\\Desktop\\工作簿1.xlsx");
		List<Map<String, Object>> reportDatas = new ArrayList<Map<String, Object>>();
		if(data_file.exists()){
			try {
				List<String[][]> datass = readExcel(new FileInputStream(data_file));
				String[][] datas = datass.get(0);
				for (int i = startRow; i < datas.length; i++) {
					try {
						Map<String,Object> map = new LinkedHashMap<String,Object>();
						for (int j = startColumn; j < datas[i].length; j++) {
							map.put(j + "", datas[i][j]);
						}
						reportDatas.add(map);
					} catch (Exception e) {
						System.err.println(e.getLocalizedMessage());
					}
				}
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			}
		}
		System.out.println(reportDatas);
		
//		Map<Integer,String> columns = new HashMap<Integer,String>();
//		columns.put(1, "title");
//		columns.put(2, "level");
//		columns.put(3, "dept_id");
//		columns.put(4, "region_id");
		
	}
}
